package com.fsnbigdata.repository;
import com.fsnbigdata.domain.Distribution;
import com.hp.hpl.jena.sparql.pfunction.library.str;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.roo.addon.layers.repository.jpa.annotations.RooJpaRepository;
import com.fsnbigdata.domain.Organism;
import io.springlets.data.jpa.repository.DetachableJpaRepository;

import java.math.BigInteger;
import java.util.List;
import java.util.Set;

import org.springframework.transaction.annotation.Transactional;

/**
 * = DistributionRepository
 *
 * TODO Auto-generated class documentation
 *
 */
@RooJpaRepository(entity = Distribution.class)
@Transactional(readOnly = true)
public interface DistributionRepository extends DetachableJpaRepository<Distribution, Long>, DistributionRepositoryCustom {

    /**
     * TODO Auto-generated method documentation
     *
     * @param organism
     * @return Long
     */
    public abstract long countByOrganism(Organism organism);

    /**
     * TODO Auto-generated method documentation
     *
     * @param id
     * @return Distribution
     */
    public abstract Distribution findOne(Long id);

    /**
     * TODO Auto-generated method documentation
     *
     * @param id
     * @return List
     */
    public abstract List<Distribution> findAll(Iterable<Long> id);

    /**
     * TODO Auto-generated method documentation
     *
     * @param distribution
     * @return Distribution
     */
    public abstract Distribution save(Distribution distribution);
    
    /**
     * TODO Auto-generated method documentation
     * @param province
     * @return
     */
    @Query(value = "select u.city from Distribution u where u.province like %:province% and u.city<>'' and u.county<>'' group by u.city")
    public abstract List<String> findAllByProvinceLike(@Param("province") String province);

    @Query(value = "select u.county from Distribution u where u.city like %:city% and u.county<>'' group by u.county")
    public abstract List<String> findAllByCityLike(@Param("city") String city);

    @Query(value = "select u.detail from Distribution u where u.county like %:county% and u.detail<>'' group by u.detail")
    public abstract List<String> findAllByCountyLike(@Param("county") String county);

    @Query(value = "select organism_id from distribution u where u.city = ?1 and u.county = ?2",nativeQuery = true)
    public abstract List<Long> findByCityAndCounty(String city,String county);

    @Query(value = "select DISTINCT organism_id from distribution u where u.province = '贵州' ",nativeQuery = true)
    public abstract List<BigInteger> geOrganismIdPronvince();

    @Query(value = "select u.city from Distribution u where u.province like CONCAT('%',?1,'%') group by u.city",nativeQuery = true)
    public abstract List<String> queryProvince(String province);

    @Query(value = "select u.county from Distribution u where u.province like CONCAT('%',?1,'%') and u.city like CONCAT('%',?2,'%') group by county",nativeQuery = true)
    public abstract List<String> queryCity(String province, String city);

    /**
     * 查询物种id集合
     * @param province  省
     * @param city  市
     * @param county    区或县
     * @return
     */
    @Query(value = "select u.organism_id from Distribution u where u.province like CONCAT('%',?1,'%') and u.city like CONCAT('%',?2,'%') and u.county like CONCAT('%',?3,'%') group by organism_id",nativeQuery = true)
    public List<Long> queryCounty(String province, String city, String county);

    /**
     * 查询贵州省下面市的物种id集合
     * @param city  市
     * @return
     */
    @Query(value = "select organism_id from distribution where province like concat('%','贵州','%') and city like concat('%',?1,'%') group by organism_id",nativeQuery = true)
    List<Object> findIdByCity(String city);

    /**
     * 查询省的物种id集合
     * @param province  省
     * @return
     */
    @Query(value = "select organism_id from distribution where province like concat('%',?1,'%') group by organism_id",nativeQuery = true)
    List<Long> queryIdByProvince(String province);

    /**
     * 通过部分物种id集合查询物种id集合
     * @param ids
     * @return
     */
    @Query(value = "select organism_id from distribution where if(?1 !='',country like concat('%',?1,'%'),1=1) AND if(?2 !='',province like concat('%',?2,'%'),1=1) AND  if(?3 !='',city like concat('%',?3,'%'),1=1) AND if(?4 !='',detail like concat('%',?4,'%'),1=1) and if(?5!='' ,organism_id in(?6),1=1)",nativeQuery = true)
    public List<Long> getIdsByCountryAndProvinceAndCityAndIds(String country,String province,String city,String detall,String sign,List<Long> ids);

    @Query(value = "select organism_id from distribution where detail like concat('%',?1,'%') group by organism_id",nativeQuery = true)
    List<Long> queryProtection(String protection);

    @Query(value = "SELECT detail FROM distribution WHERE detail LIKE concat('%','保护区','%') GROUP BY detail",nativeQuery = true)
    List<String> getProtection();

    /**
     * 通过部分物种id集合查询物种id集合
     * @return
     */
    @Query(value = "select organism_id from distribution WHERE if(?1 !='',county like concat('%',?1,'%'),1=1) AND if(?2 !='',province like concat('%',?2,'%'),1=1) AND if(?3 !='',city like concat('%',?3,'%'),1=1) GROUP BY organism_id",nativeQuery = true)
    public List<Long> getIds(String county,String province,String city);

    @Query(value = "SELECT county FROM distribution WHERE if(?1!='',province=?1,1=1) and if(?2!='',city=?2,1=1) GROUP BY county",nativeQuery = true)
    List<String> getByCity(String province, String city);

    @Query(value = "SELECT city FROM distribution WHERE if(?1!='',province=?1,1=1) GROUP BY city",nativeQuery = true)
    List<String> getByProvince(String province);


}
